#这里测试数据删除了新建的数据，所以会看起来数据表没有变化
import pymssql
from datetime import datetime  # 引入时间，python 中的date是字符串
import sys
sys.stdout.reconfigure(encoding='utf-8')

###连接函数
def get_database_connection():
    try:
        # 尝试建立数据库连接
        conn = pymssql.connect(server='127.0.0.1', user='sa', password='123456', database='system')
        # 如果连接成功，打印成功消息
        print("数据库连接成功")
        return conn
    except pymssql.Error as e:
        # 如果连接失败，打印错误信息
        print("数据库连接失败：", e)
        return None

# 使用函数并检查连接
connection = get_database_connection()
if connection:
    # 如果连接成功，可以进行后续的数据库操作
    print("可以进行数据库操作")
else:
    # 如果连接失败，需要处理错误
    print("无法进行数据库操作")

#学生实体类
class Student:
    def __init__(self, student_id, name, date_of_birth, gender, graduation_date, id_number, birthplace, undergraduate_major, email, phone, undergraduate_institution, institution_type, resume):
        self.student_id = student_id
        self.name = name
        self.date_of_birth = date_of_birth
        self.gender = gender
        self.graduation_date = graduation_date
        self.id_number = id_number
        self.birthplace = birthplace
        self.undergraduate_major = undergraduate_major
        self.email = email
        self.phone = phone
        self.undergraduate_institution = undergraduate_institution
        self.institution_type = institution_type
        self.resume = resume

#Tutor_Info实体类
class TutorInfo:
    def __init__(self, tutor_id, tutor_name, tutor_title, tutor_photo, tutor_introduction, tutor_email, tutor_phone, tutor_discipline):
        self.tutor_id = tutor_id
        self.tutor_name = tutor_name
        self.tutor_title = tutor_title
        self.tutor_photo = tutor_photo
        self.tutor_introduction = tutor_introduction
        self.tutor_email = tutor_email
        self.tutor_phone = tutor_phone
        self.tutor_discipline = tutor_discipline

#Choice实体类
class Choice:
    def __init__(self, choice_id, student_id, first_choice_instructor, second_choice_instructor, third_choice_instructor):
        self.choice_id = choice_id
        self.student_id = student_id
        self.first_choice_instructor = first_choice_instructor
        self.second_choice_instructor = second_choice_instructor
        self.third_choice_instructor = third_choice_instructor

#Score类
class Score:
    def __init__(self, score_id, student_id, initial_score, retest_score):
        self.score_id = score_id
        self.student_id = student_id
        self.initial_score = initial_score
        self.retest_score = retest_score

#初试成绩类
class InitialSubjectScore:
    def __init__(self, subject_score_id, student_id, subject_name, subject_score):
        self.subject_score_id = subject_score_id
        self.student_id = student_id
        self.subject_name = subject_name
        self.subject_score = subject_score

#复试成绩类
class RetestSubjectScore:
    def __init__(self, subject_score_id, student_id, subject_name, subject_score):
        self.subject_score_id = subject_score_id
        self.student_id = student_id
        self.subject_name = subject_name
        self.subject_score = subject_score

class StudentDao:
    def get_student(self, student_id):
        pass

    def add_student(self, student):
        pass

    def update_student(self, student):
        pass

    def delete_student(self, student_id):
        pass

class TutorInfoDao:
    def get_tutor_info(self, tutor_id):
        pass

    def add_tutor_info(self, tutor_info):
        pass

    def update_tutor_info(self, tutor_info):
        pass

    def delete_tutor_info(self, tutor_id):
        pass

class ChoiceDao:
    def get_choice(self, choice_id):
        pass

    def add_choice(self, choice):
        pass

    def update_choice(self, choice):
        pass

    def delete_choice(self, choice_id):
        pass

class ScoreDao:
    def get_score(self, score_id):
        pass

    def add_score(self, score):
        pass

    def update_score(self, score):
        pass

    def delete_score(self, score_id):
        pass

class InitialSubjectScoreDao:
    def get_initial_subject_score(self, subject_score_id):
        pass

    def add_initial_subject_score(self, initial_subject_score):
        pass

    def update_initial_subject_score(self, initial_subject_score):
        pass

    def delete_initial_subject_score(self, subject_score_id):
        pass

class RetestSubjectScoreDao:
    def get_retest_subject_score(self, subject_score_id):
        pass

    def add_retest_subject_score(self, retest_subject_score):
        pass

    def update_retest_subject_score(self, retest_subject_score):
        pass

    def delete_retest_subject_score(self, subject_score_id):
        pass


class StudentDaoImpl(StudentDao):
    def __init__(self, db_connection):
        self.db_connection = db_connection

    def get_student(self, student_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM Students WHERE StudentID = %s", (student_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return Student(*result)
        return None

    def add_student(self, student):
        cursor = self.db_connection.cursor()
        cursor.execute("INSERT INTO Students (StudentID, Name, DateOfBirth, Gender, GraduationDate, IDNumber, Birthplace, UndergraduateMajor, Email, Phone, UndergraduateInstitution, InstitutionType, Resume) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                     (student.student_id, student.name, student.date_of_birth, student.gender, student.graduation_date, student.id_number, student.birthplace, student.undergraduate_major, student.email, student.phone, student.undergraduate_institution, student.institution_type, student.resume))
        self.db_connection.commit()
        cursor.close()

    def update_student(self, student):
        cursor = self.db_connection.cursor()
        cursor.execute("UPDATE Students SET Name = %s, DateOfBirth = %s, Gender = %s, GraduationDate = %s, IDNumber = %s, Birthplace = %s, UndergraduateMajor = %s, Email = %s, Phone = %s, UndergraduateInstitution = %s, InstitutionType = %s, Resume = %s WHERE StudentID = %s",
                     (student.name, student.date_of_birth, student.gender, student.graduation_date, student.id_number, student.birthplace, student.undergraduate_major, student.email, student.phone, student.undergraduate_institution, student.institution_type, student.resume, student.student_id))
        self.db_connection.commit()
        cursor.close()

    def delete_student(self, student_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM Students WHERE StudentID = %s", (student_id,))
        self.db_connection.commit()
        cursor.close()

class TutorInfoDaoImpl(TutorInfoDao):
    def __init__(self, db_connection):
        self.db_connection = db_connection

    def get_tutor_info(self, tutor_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM Tutor_Info WHERE Tutor_ID = %s", (tutor_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return TutorInfo(*result)
        return None

    def add_tutor_info(self, tutor_info):
        cursor = self.db_connection.cursor()
        cursor.execute("INSERT INTO Tutor_Info (Tutor_ID, Tutor_Name, Tutor_Title, Tutor_Photo, Tutor_Introduction, Tutor_Email, Tutor_Phone, Tutor_Discipline) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
                     (tutor_info.tutor_id, tutor_info.tutor_name, tutor_info.tutor_title, tutor_info.tutor_photo, tutor_info.tutor_introduction, tutor_info.tutor_email, tutor_info.tutor_phone, tutor_info.tutor_discipline))
        self.db_connection.commit()
        cursor.close()

    def update_tutor_info(self, tutor_info):
        cursor = self.db_connection.cursor()
        cursor.execute("UPDATE Tutor_Info SET Tutor_Name = %s, Tutor_Title = %s, Tutor_Photo = %s, Tutor_Introduction = %s, Tutor_Email = %s, Tutor_Phone = %s, Tutor_Discipline = %s WHERE Tutor_ID = %s",
                     (tutor_info.tutor_name, tutor_info.tutor_title, tutor_info.tutor_photo, tutor_info.tutor_introduction, tutor_info.tutor_email, tutor_info.tutor_phone, tutor_info.tutor_discipline, tutor_info.tutor_id))
        self.db_connection.commit()
        cursor.close()

    def delete_tutor_info(self,tutor_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM Tutor_Info WHERE Tutor_ID = %s", (tutor_id,))
        self.db_connection.commit()
        cursor.close()

class ChoiceDaoImpl(ChoiceDao):
    def __init__(self, db_connection):  # 确保构造函数接受 db_connection 参数
        self.db_connection = db_connection

    def get_choice(self, choice_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM Choices WHERE ChoiceID = %s", (choice_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return Choice(*result)
        return None

    def add_choice(self, choice):
        cursor = self.db_connection.cursor()
        cursor.execute("INSERT INTO Choices (ChoiceID, StudentID, FirstChoiceInstructor, SecondChoiceInstructor, ThirdChoiceInstructor) VALUES (%s, %s, %s, %s, %s)",
                     (choice.choice_id, choice.student_id, choice.first_choice_instructor, choice.second_choice_instructor, choice.third_choice_instructor))
        self.db_connection.commit()
        cursor.close()

    def update_choice(self, choice):
        cursor = self.db_connection.cursor()
        cursor.execute("UPDATE Choices SET StudentID = %s, FirstChoiceInstructor = %s, SecondChoiceInstructor = %s, ThirdChoiceInstructor = %s WHERE ChoiceID = %s",
                     (choice.student_id, choice.first_choice_instructor, choice.second_choice_instructor, choice.third_choice_instructor, choice.choice_id))
        self.db_connection.commit()
        cursor.close()

    def delete_choice(self, choice_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM Choices WHERE ChoiceID = %s", (choice_id,))
        self.db_connection.commit()
        cursor.close()

class ScoreDaoImpl(ScoreDao):
    def __init__(self, db_connection):  # 确保构造函数接受 db_connection 参数
        self.db_connection = db_connection

    def get_score(self, score_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM Scores WHERE ScoreID = %s", (score_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return Score(*result)
        return None

    def add_score(self, score):
        cursor = self.db_connection.cursor()
        cursor.execute("INSERT INTO Scores (ScoreID, StudentID, InitialScore, RetestScore) VALUES (%s, %s, %s, %s)",
                     (score.score_id, score.student_id, score.initial_score, score.retest_score))
        self.db_connection.commit()
        cursor.close()

    def update_score(self, score):
        cursor = self.db_connection.cursor()
        cursor.execute("UPDATE Scores SET StudentID = %s, InitialScore = %s, RetestScore = %s WHERE ScoreID = %s",
                     (score.student_id, score.initial_score, score.retest_score, score.score_id))
        self.db_connection.commit()
        cursor.close()

    def delete_score(self, score_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM Scores WHERE ScoreID = %s", (score_id,))
        self.db_connection.commit()
        cursor.close()

class InitialSubjectScoreDaoImpl(InitialSubjectScoreDao):
    def __init__(self, db_connection):  # 确保构造函数接受 db_connection 参数
        self.db_connection = db_connection

    def get_initial_subject_score(self, subject_score_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM InitialSubjectScores WHERE SubjectScoreID = %s", (subject_score_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return InitialSubjectScore(*result)
        return None

    def add_initial_subject_score(self, initial_subject_score):
        cursor = self.db_connection.cursor()
        cursor.execute(
            "INSERT INTO InitialSubjectScores (SubjectScoreID, StudentID, SubjectName, SubjectScore) VALUES (%s, %s, %s, %s)",
            (initial_subject_score.subject_score_id, initial_subject_score.student_id,
             initial_subject_score.subject_name, initial_subject_score.subject_score))
        self.db_connection.commit()
        cursor.close()

    def update_initial_subject_score(self, initial_subject_score):
        cursor = self.db_connection.cursor()
        cursor.execute(
            "UPDATE InitialSubjectScores SET StudentID = %s, SubjectName = %s, SubjectScore = %s WHERE SubjectScoreID = %s",
            (initial_subject_score.student_id, initial_subject_score.subject_name, initial_subject_score.subject_score,
             initial_subject_score.subject_score_id))
        self.db_connection.commit()
        cursor.close()

    def delete_initial_subject_score(self, subject_score_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM InitialSubjectScores WHERE SubjectScoreID = %s", (subject_score_id,))
        self.db_connection.commit()
        cursor.close()

class RetestSubjectScoreDaoImpl(RetestSubjectScoreDao):
    def __init__(self, db_connection):  # 确保构造函数接受 db_connection 参数
        self.db_connection = db_connection

    def get_retest_subject_score(self, subject_score_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM RetestSubjectScores WHERE SubjectScoreID = %s", (subject_score_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return RetestSubjectScore(*result)
        return None

    def add_retest_subject_score(self, retest_subject_score):
        cursor = self.db_connection.cursor()
        cursor.execute(
            "INSERT INTO RetestSubjectScores (SubjectScoreID, StudentID, SubjectName, SubjectScore) VALUES (%s, %s, %s, %s)",
            (retest_subject_score.subject_score_id, retest_subject_score.student_id, retest_subject_score.subject_name,
             retest_subject_score.subject_score))
        self.db_connection.commit()
        cursor.close()

    def update_retest_subject_score(self, retest_subject_score):
        cursor = self.db_connection.cursor()
        cursor.execute(
            "UPDATE RetestSubjectScores SET StudentID = %s, SubjectName = %s, SubjectScore = %s WHERE SubjectScoreID = %s",
            (retest_subject_score.student_id, retest_subject_score.subject_name, retest_subject_score.subject_score,
             retest_subject_score.subject_score_id))
        self.db_connection.commit()
        cursor.close()

    def delete_retest_subject_score(self, subject_score_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM RetestSubjectScores WHERE SubjectScoreID = %s", (subject_score_id,))
        self.db_connection.commit()
        cursor.close()



# 连接函数
def get_database_connection():
    try:
        # 尝试建立数据库连接
        conn = pymssql.connect(server='127.0.0.1', user='sa', password='123456', database='system')
        # 如果连接成功，打印成功消息
        print("数据库连接成功")
        return conn
    except pymssql.Error as e:
        # 如果连接失败，打印错误信息
        print("数据库连接失败：", e)
        return None

# 测试数据库持久层的函数
def test_database_persistence():
    # 获取数据库连接
    connection = get_database_connection()
    if not connection:
        print("数据库连接失败，无法进行测试")
        return

    # 创建持久层对象
    student_dao = StudentDaoImpl(connection)
    tutor_info_dao = TutorInfoDaoImpl(connection)
    choice_dao = ChoiceDaoImpl(connection)
    score_dao = ScoreDaoImpl(connection)
    initial_subject_score_dao = InitialSubjectScoreDaoImpl(connection)
    retest_subject_score_dao = RetestSubjectScoreDaoImpl(connection)

    # 测试学生实体的持久层
    test_student_persistence(student_dao)
    # 测试导师实体的持久层
    # test_tutor_info_persistence(tutor_info_dao)
    # 测试选择实体的持久层
    test_choice_persistence(choice_dao)
    # 测试成绩实体的持久层
    test_score_persistence(score_dao)
    # 测试初试成绩实体的持久层
    test_initial_subject_score_persistence(initial_subject_score_dao)
    # 测试复试成绩实体的持久层
    test_retest_subject_score_persistence(retest_subject_score_dao)

    # 关闭数据库连接
    connection.close()

# 测试学生实体持久层
def test_student_persistence(student_dao):
    student_id = 5  # 使用一个不存在的ID来避免主键冲突
    student = Student(student_id, '王七', '1998-01-01', 'Male', '2024-07-01', '123456789012345', '成都', '网络工程', 'wangqi@example.com', '14000140000', '北京林业大学', '全日制', '网络工程技能熟练。')
    student_dao.add_student(student)
    retrieved_student = student_dao.get_student(student_id)
    print(f"Retrieved Student: {retrieved_student.name}")  # 打印检索到的学生姓名
    student_dao.delete_student(student_id)

# # 测试导师实体持久层
# def test_tutor_info_persistence(tutor_info_dao):
#     tutor_id = 5  # 使用一个不存在的ID来避免主键冲突
#     tutor_info = TutorInfo(tutor_id, '冯教授', '教授', None, '专注于网络安全。', 'fengprof@example.com', '10086100865', '网络工程')
#     tutor_info_dao.add_tutor_info(tutor_info)
#     retrieved_tutor_info = tutor_info_dao.get_tutor_info(tutor_id)
#     print(f"Retrieved Tutor: {retrieved_tutor_info.tutor_name}")  # 打印检索到的导师姓名
#     #tutor_info_dao.delete_tutor_info(tutor_id)

# 测试选择实体持久层
def test_choice_persistence(choice_dao):
    choice_id = 5  # 使用一个不存在的ID来避免主键冲突
    choice = Choice(choice_id, 1, 2, 3, 4)  # 假设学生ID为1
    choice_dao.add_choice(choice)
    retrieved_choice = choice_dao.get_choice(choice_id)
    print(f"Retrieved Choice: {retrieved_choice.first_choice_instructor}")  # 打印检索到的首选导师
    choice_dao.delete_choice(choice_id)

# 测试成绩实体持久层
def test_score_persistence(score_dao):
    score_id = 5  # 使用一个不存在的ID来避免主键冲突
    score = Score(score_id, 1, 280.00, 88.50)  # 假设学生ID为1
    score_dao.add_score(score)
    retrieved_score = score_dao.get_score(score_id)
    print(f"Retrieved Score: {retrieved_score.initial_score}")  # 打印检索到的初试成绩
    score_dao.delete_score(score_id)

# 测试初试成绩实体持久层
def test_initial_subject_score_persistence(initial_subject_score_dao):
    subject_score_id = 17  # 使用一个不存在的ID来避免主键冲突
    initial_subject_score = InitialSubjectScore(subject_score_id, 1, '科目五', 90)  # 假设学生ID为1
    initial_subject_score_dao.add_initial_subject_score(initial_subject_score)
    retrieved_initial_subject_score = initial_subject_score_dao.get_initial_subject_score(subject_score_id)
    print(f"Retrieved Initial Subject Score: {retrieved_initial_subject_score.subject_name}")  # 打印检索到的初试科目名称
    initial_subject_score_dao.delete_initial_subject_score(subject_score_id)

# 测试复试成绩实体持久层
def test_retest_subject_score_persistence(retest_subject_score_dao):
    subject_score_id = 13  # 使用一个不存在的ID来避免主键冲突
    retest_subject_score = RetestSubjectScore(subject_score_id, 1, '英语口语', 95)  # 假设学生ID为1
    retest_subject_score_dao.add_retest_subject_score(retest_subject_score)
    retrieved_retest_subject_score = retest_subject_score_dao.get_retest_subject_score(subject_score_id)
    print(f"Retrieved Retest Subject Score: {retrieved_retest_subject_score.subject_name}")  # 打印检索到的复试科目名称
    retest_subject_score_dao.delete_retest_subject_score(subject_score_id)

# 运行测试函数
test_database_persistence()